<?php

namespace app\common\service;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

use Exception;

class Excel
{
    /**
     * 导出Excel
     *
     * @param string $title sheet 标题
     * @param array $tableData 表格数据
     * @param array $cellName 表格头、数据字段等信息
     * @param string $filePath 保存文件路径
     * @param string $fileName 保存文件名
     * @param array $options 选项
     * @param bool $isDownload 是否下载
     */
    public static function exportExcel($title, $tableData, $cellName, $filePath, $fileName, $options = [], $isDownload = false)
    {
        set_time_limit(0);
        try {
            if (!$tableData) {
                return [
                    'status' => 0,
                    'msg' => 'empty'
                ];
            }
            $spreadsheet = new Spreadsheet();
            /*// 设置默认文字居左，上下居中
            $styleArray = [
                'alignment' => [
                    'horizontal' => Alignment::HORIZONTAL_LEFT,
                    'vertical' => Alignment::VERTICAL_CENTER,
                ],
            ];
            $spreadsheet->getDefaultStyle()->applyFromArray($styleArray);*/
            $activeSheet = $spreadsheet->setActiveSheetIndex(0);
            $spreadsheet->getActiveSheet()->setTitle($title);

            // 打印设置
            if (isset($options['print']) && $options['print']) {
                // 设置打印为A4效果
                $activeSheet->getPageSetup()->setPaperSize(PageSetup:: PAPERSIZE_A4);
                // 设置打印时边距
                $pValue = 1 / 2.54;
                $activeSheet->getPageMargins()->setTop($pValue / 2);
                $activeSheet->getPageMargins()->setBottom($pValue * 2);
                $activeSheet->getPageMargins()->setLeft($pValue / 2);
                $activeSheet->getPageMargins()->setRight($pValue / 2);
            }
            self::setExcelDatas($spreadsheet, $tableData, $cellName, 2, $options);
            // 设置单元格边框，整个表格设置即可，必须在数据填充后才可以获取到最大行列
            if (isset($options['border']) && $options['border']) {
                $border = [
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => Border::BORDER_THIN, // 设置border样式
                            'color' => ['argb' => Color::COLOR_BLACK], // 设置border颜色
                        ],
                    ],
                ];
                $setBorder = 'A1:' . $activeSheet->getHighestColumn() . $activeSheet->getHighestRow();
                $activeSheet->getStyle($setBorder)->applyFromArray($border);
            }

            // 多个 sheet
            if (isset($options['sheetDatas']) && $options['sheetDatas']) {
                $sheetDatas = $options['sheetDatas'];
                foreach ($sheetDatas as $sheetKey => $sheetData) {
                    if (isset($sheetData['tableData']) && isset($sheetData['cellName'])) {
                        $sheetKey++;
                        $spreadsheet->createSheet();
                        $spreadsheet->setActiveSheetIndex($sheetKey);
                        $spreadsheet->getActiveSheet()->setTitle($sheetData['title'] ?? 'sheet_' . $sheetKey);
                        self::setExcelDatas($spreadsheet, $sheetData['tableData'], $sheetData['cellName'], 2);
                    }
                }
            }

            // 检查文件或目录是否存在
            if (!file_exists($filePath)) {
                mkdir($filePath, 0777, true);
            }
            ob_clean();
            ob_start();
            $writer = new Xlsx($spreadsheet);
            $suffix = '.xlsx'; // .xls
            $writer->save($fileName . $suffix); // or .xlsx
            // 释放内存
            $spreadsheet->disconnectWorksheets();
            ob_end_flush();

            $newFileName = substr($fileName, strripos($fileName, 'uploads/') + 8);
            $newFileName = full_file_url($newFileName);
            $fileUrl = $newFileName . $suffix;
            if ($isDownload) {
                /*$title = iconv('utf-8', 'gb2312', $title); // 文件名称
                $downFileName = basename($fileName, '.' . pathinfo($fileName, PATHINFO_EXTENSION));
                header('pragma:public');
                header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $title . '.xls"');
                header("Content-Disposition:attachment;filename=$downFileName.xls"); // attachment 新窗口打印,inline 本窗口打印

                $writer->save('php://output');*/
                //exit;

                header('location:' . $fileUrl);
                exit;
            } else {
                return [
                    'status' => 1,
                    'msg' => 'success',
                    'url' => $fileUrl
                ];
            }
            //sleep(1);
        } catch (\Exception $e) {
            return [
                'status' => 0,
                'msg' => $e->getMessage()
            ];
        }
    }

    private static function setExcelDatas($spreadsheet, $tableData, $cellArray, $index = 2, $options = [])
    {
        //$limit = ceil(count($tableData) / 1000);
        $startIndex = $index;
        $isObj = is_object($tableData);
        foreach ($tableData as $rowKey => $row) {
            self::setExcelData($spreadsheet, $cellArray, $row, $rowKey, $index, $startIndex, $isObj, $options);
            $index++;
        }
    }

    private static function setExcelData($spreadsheet, $cellArray, $item, $itemKey, $index, $startIndex, $isObj = false, $options = [])
    {
        //$fontName = 'Arial';
        $fontName = '宋体';
        $isWrapText = false; // 是否存在自动换行的列
        // 设置列数据
        foreach ($cellArray as $key => $cell) {
            if (!isset($cell['title']) || !isset($cell['field'])) {
                throw new \Exception('参数错误！');
            }
            $cellTitle = $cell['title'];
            $cellField = $cell['field'];
            $chrKey = self::intToChr($key);
            if ($itemKey == 0) {
                $spreadsheet->getDefaultStyle()->getFont()->setSize(10);
                $spreadsheet->getDefaultStyle()->getFont()->setName($fontName);
                if ($cellTitle) {
                    // 设置表头
                    $spreadsheet->getActiveSheet()->setCellValue($chrKey . ($index - 1), $cellTitle);
                    $spreadsheet->getActiveSheet()->getStyle($chrKey . ($index - 1))->getFont()->setBold(true);
                    if (isset($options['headers']) && isset($options['headers']['height'])) {
                        $spreadsheet->getActiveSheet()->getRowDimension($index - 1)->setRowHeight($options['headers']['height']);
                    } else {
                        $spreadsheet->getActiveSheet()->getRowDimension($index - 1)->setRowHeight(16);
                    }
                    $spreadsheet->getActiveSheet()->getStyle($chrKey . ($index - 1))->applyFromArray([
                        'alignment' => [
                            'horizontal' => Alignment::HORIZONTAL_CENTER, // 水平居中
                            'vertical' => Alignment::VERTICAL_CENTER, // 垂直居中
                        ]
                    ]);
                }
                // 下面这些只需要设置一次
                if (isset($cell['width']) && is_numeric($cell['width'])) {
                    // 设置列宽
                    $spreadsheet->getActiveSheet()->getColumnDimension($chrKey)->setWidth($cell['width']);
                } else {
                    $spreadsheet->getActiveSheet()->getColumnDimension($chrKey)->setAutoSize(true); // 自动计算列宽
                }
            }

            //$spreadsheet->getActiveSheet()->mergeCells('A' . ($index) . ':B' . ($index));// 合并单元格列
            //$spreadsheet->getActiveSheet()->getStyle($chrKey . ($index))->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_TEXT);
            //$spreadsheet->getActiveSheet()->getStyle($chrKey . ($index))->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
            if ($isObj) {
                try {
                    $spreadsheet->getActiveSheet()->setCellValueExplicit($chrKey . ($index), $item->{$cellField}(), DataType::TYPE_STRING);
                    //$spreadsheet->getActiveSheet()->setCellValue($chrKey . ($index), $item->{$cellField}()); // Custom Attribute function
                } catch (Exception $e) {
                    $spreadsheet->getActiveSheet()->setCellValueExplicit($chrKey . ($index), $item->{$cellField}, DataType::TYPE_STRING);
                    //$spreadsheet->getActiveSheet()->setCellValue($chrKey . ($index), $item->{$cellField});
                }
            } else {
                if (isset($cell['type']) && $cell['type'] == 'image') {
                    if (isset($item[$cellField]) && $item[$cellField]) {
                        $imgWidth = 50;
                        $imgHeight = 50;
                        if (file_exists($item[$cellField])) {
                            $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                            //$drawing->setName('image');
                            //$drawing->setDescription('image');
                            $drawing->setPath($item[$cellField]);
                        } else {
                            $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing();
                            $imgResource = imagecreatefromstring(file_get_contents($item[$cellField]));
                            $drawing->setImageResource($imgResource);
                            //$drawing->setRenderingFunction(\PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::RENDERING_JPEG);
                            //$drawing->setMimeType(\PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::MIMETYPE_DEFAULT);
                        }
                        $drawing->setWidth($imgWidth); // 设置图片宽度
                        $drawing->setHeight($imgHeight); // 设置图片高度
                        $drawing->setCoordinates($chrKey . ($index)); // 设置图片要插入的单元格
                        $drawing->setOffsetX(10); // 设置图片偏移量
                        $drawing->setOffsetY(10);
                        $drawing->setRotation(0);
                        $drawing->getShadow()->setVisible(true);
                        $drawing->getShadow()->setDirection(50);
                        // 将图片添加到工作表中
                        $drawing->setWorksheet($spreadsheet->getActiveSheet());
                    }
                } else {
                    // 解决长数字串显示为科学计数（1.23E+12）
                    $spreadsheet->getActiveSheet()->setCellValueExplicit($chrKey . ($index), $item[$cellField], DataType::TYPE_STRING);
                    //$spreadsheet->getActiveSheet()->setCellValue($chrKey . ($index), $item[$cellField]);
                }
            }

            if ($index > $startIndex) {
                // 是否要合并行
                //$rowspan_field = $cellField . '_rowspan'; // 默认
                if (isset($cell['rowspanField']) && $cell['rowspanField']) {
                    $rowspan_field = $cell['rowspanField'];
                    if (isset($item[$rowspan_field]) && $item[$rowspan_field] > 0) {
                        $rowspan = $item[$rowspan_field] - 1;
                        $spreadsheet->getActiveSheet()->mergeCells($chrKey . ($index - $rowspan) . ':' . $chrKey . ($index));
                        // 居中显示
                        /*$spreadsheet->getActiveSheet()->getStyle($chrKey . ($index - $rowspan))->applyFromArray([
                            'alignment' => [
                                //'horizontal' => Alignment::HORIZONTAL_CENTER, // 水平居中
                                'vertical' => Alignment::VERTICAL_CENTER // 垂直居中
                            ]
                        ]);*/
                    }
                }
                // 是否要合并列
                if (isset($cell['colspanField']) && $cell['colspanField']) {
                    $colspan_field = $cell['colspanField'];
                    if (isset($item[$colspan_field]) && $item[$colspan_field] > 0) {
                        $colspan = $item[$colspan_field] - 1;
                        $chrKeyNext = self::intToChr($key + $colspan);
                        $spreadsheet->getActiveSheet()->mergeCells($chrKey . ($index) . ':' . $chrKeyNext . ($index));
                    }
                }
            }
            // 换行
            if (isset($cell['wrapText']) && $cell['wrapText']) {
                $spreadsheet->getActiveSheet()->getStyle($chrKey . $index)->getAlignment()->setWrapText(true); // 自动换行
                $isWrapText = true;
            }
            // 设置居中
            if (isset($cell['alignCenter']) && !empty($cell['alignCenter'])) {
                if ($cell['alignCenter'] == 'center') {
                    $spreadsheet->getActiveSheet()->getStyle($chrKey . $index)->applyFromArray([
                        'alignment' => [
                            'horizontal' => Alignment::HORIZONTAL_CENTER, // 水平居中
                            'vertical' => Alignment::VERTICAL_CENTER, // 垂直居中
                        ]
                    ]);
                } elseif ($cell['alignCenter'] == 'horizontal') {
                    $spreadsheet->getActiveSheet()->getStyle($chrKey . $index)->applyFromArray([
                        'alignment' => [
                            'horizontal' => Alignment::HORIZONTAL_CENTER
                        ]
                    ]);
                } elseif ($cell['alignCenter'] == 'vertical') {
                    //
                }
            } else {
                // 默认垂直居中
                $spreadsheet->getActiveSheet()->getStyle($chrKey . $index)->applyFromArray([
                    'alignment' => [
                        'vertical' => Alignment::VERTICAL_CENTER
                    ]
                ]);
            }
            // 设置字体色
            if (isset($cell['fontColorField']) && $cell['fontColorField']) {
                $font_color_field = $cell['fontColorField'];
                if (isset($item[$font_color_field]) && $item[$font_color_field]) {
                    $spreadsheet->getActiveSheet()->getStyle($chrKey . ($index))->getFont()->getColor()->setARGB($item[$font_color_field]);
                }
            }
            // 设置背景色
            if (isset($cell['bgColorField']) && $cell['bgColorField']) {
                $bg_color_field = $cell['bgColorField'];
                if (isset($item[$bg_color_field]) && $item[$bg_color_field]) {
                    $spreadsheet->getActiveSheet()->getStyle($chrKey . ($index))->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB($item[$bg_color_field]);
                }
            }
            // 设置加粗
            if (isset($cell['boldField']) && $cell['boldField']) {
                $bold_field = $cell['boldField'];
                if (isset($item[$bold_field]) && $item[$bold_field]) {
                    $spreadsheet->getActiveSheet()->getStyle($chrKey . $index)->getFont()->setBold(true);
                }
            }
        }
        // 判断是否有表头
        if (isset($cellArray[0]['title']) && $cellArray[0]['title']) {
            // 判断是否设置了自动换行，没设置自动换行才设置行高
            if (!$isWrapText) {
                // 每行只需设置一次
                if (isset($item['height']) && $item['height']) {
                    $spreadsheet->getActiveSheet()->getRowDimension($index)->setRowHeight($item['height']);
                } else {
                    $spreadsheet->getActiveSheet()->getRowDimension($index)->setRowHeight(14.25);
                }
            }
        }
        if (isset($imgHeight) && $imgHeight) {
            $spreadsheet->getActiveSheet()->getRowDimension($index)->setRowHeight($imgHeight);
        }
        // 每执行1000条数据
        if ($index % 1000 == 0) {
            sleep(1);
        }
        //$index++; // 无效
    }

    /**
     * Export（大数据导出）
     */
    public static function export($title, $model, $where, $cellName, $filePath, $fileName, $isDownload = false)
    {
        set_time_limit(0); // 超时
        ini_set('memory_limit', '1024M'); // 内存溢出

        try {
            $cellArray = $cellName;

            // 计算要导出总数据条数
            $totalNums = $model->where($where)->count('id');
            // 一个Excel量，假设是十万
            $accessNum = '30000';
            // Excel个数
            $excelNum = ceil($totalNums / $accessNum);
            $lastId = 0;
            $fileNames = [];
            $suffix = '.xlsx'; // .xls
            for ($j = 1; $j <= $excelNum; $j++) {
                $spreadsheet = new Spreadsheet();
                $spreadsheet->setActiveSheetIndex(0);
                $spreadsheet->getActiveSheet()->setTitle($title);

                if ($excelNum <= 1) {
                    $saveFileName = $fileName;
                } else {
                    //$saveFileName = $fileName . '_0' . $j; // 会创建没有用的空目录
                    $saveFileName = config('path.upload_path') . 'zip/' . date('YmdHis') . '_0' . $j;
                    $fileNames[] = $saveFileName . $suffix;
                }
                // 每次查询的条数
                $perSize = 10000;
                // 分批导的次数
                $pages = ceil($accessNum / $perSize);
                // 主体内容
                $index = 2;
                $startIndex = $index;
                for ($i = 0; $i < $pages; $i++) {
                    $newWhere = $where;
                    $newWhere[] = ['id', '>', $lastId];
                    $tableData = $model->where($newWhere)->limit(0, $perSize)->field('*')->select()->toarray();
                    if (count($tableData) < 1) {
                        continue;
                    }
                    $isObj = is_object($tableData);
                    foreach ($tableData as $itemKey => &$item) {
                        $item = $model->formatItem($item);
                        $lastId = $item['id']; // 用LastId的方式来做分页查询，增加查询效率

                        self::setExcelData($spreadsheet, $cellArray, $item, $itemKey, $index, $startIndex, $isObj);
                        $index++;
                    }
                    unset($tableData); // 释放变量的内存

                    if ($excelNum <= 1) {
                        // 检查文件或目录是否存在
                        if (!file_exists($filePath)) {
                            mkdir($filePath, 0777, true);
                        }
                    }

                    $writer = new Xlsx($spreadsheet);
                    $writer->save($saveFileName . $suffix); // or .xlsx

                    sleep(1);
                }
            }

            if ($fileNames) {
                // 进行多个文件压缩
                $zip = new \ZipArchive();
                $zipFilePath = config('path.upload_path') . 'zip';
                // 检查文件或目录是否存在
                if (!file_exists($zipFilePath)) {
                    mkdir($zipFilePath, 0777, true);
                }
                $zipFileName = $zipFilePath . '/' . date('YmdHis') . '.zip';
                // 打开压缩包
                $zipRes = $zip->open($zipFileName, \ZipArchive::CREATE); // CREATE 如果指定的zip文件不存在，则新建一个
                if ($zipRes) {
                    foreach ($fileNames as $file) {
                        $zip->addFile($file, basename($file)); // 向压缩包中添加文件
                    }
                }
                $zip->close(); // 关闭压缩包
                foreach ($fileNames as $file) {
                    unlink($file); // 删除csv临时文件
                }
                $newZipFileName = substr($zipFileName, strripos($zipFileName, 'uploads/') + 8);
                $newZipFileName = full_file_url($newZipFileName);
                if ($isDownload) {
                    header('location:' . $newZipFileName);
                    exit;
                } else {
                    return [
                        'status' => 1,
                        'msg' => 'success',
                        'url' => $newZipFileName
                    ];
                }
            }

            $newFileName = substr($fileName, strripos($fileName, 'uploads/') + 8);
            $newFileName = full_file_url($newFileName);
            $fileUrl = $newFileName . $suffix;
            if ($isDownload) {
                header('location:' . $fileUrl);
                exit;
            }

            return [
                'status' => 1,
                'msg' => 'success',
                'url' => $fileUrl
            ];
        } catch (\Exception $e) {
            return [
                'status' => 0,
                'msg' => $e->getMessage()
            ];
        }
    }

    /**
     * 原始导出（大数据）
     */
    public static function exportCsv($model, $where, $columns, $filePath, $fileName, $isDownload = false)
    {
        set_time_limit(0); // 超时
        ini_set('memory_limit', '1024M'); // 内存溢出

        try {
            // 设置表头编码
            //$columns = ['序号', '名称', '创建时间'];
            // CSV的Excel支持GBK编码，一定要转换，否则乱码
            /*foreach ($columns as &$column) {
                $column = iconv('utf-8', 'gbk', $column);
            }*/
            mb_convert_variables('GBK', 'UTF-8', $columns);

            // 计算要导出总数据条数
            $totalNums = $model->where($where)->count('id');
            // 一个Excel量，假设是十万
            $accessNum = '10000';
            // Excel个数
            $excelNum = ceil($totalNums / $accessNum);
            $lastId = 0;
            $fileNames = [];
            $suffix = '.csv'; // '.xlsx' 只有一列
            for ($j = 1; $j <= $excelNum; $j++) {
                if ($excelNum <= 1) {
                    $saveFileName = $fileName;
                } else {
                    $saveFileName = $fileName . '_0' . $j;
                    $fileNames[] = $saveFileName . $suffix;
                }
                // 每次查询的条数
                $perSize = 1000;
                // 分批导的次数
                $pages = ceil($accessNum / $perSize);
                if (!file_exists($filePath)) {
                    mkdir($filePath, 0777, true);
                }
                // 打开文件流
                $fp = fopen($saveFileName . $suffix, 'a');
                //$fp = fopen('php://output', 'a');
                // 将数据格式化为CSV格式并写入到文件流中
                fputcsv($fp, $columns);

                // 主体内容
                for ($i = 0; $i < $pages; $i++) {
                    $where[] = ['id', '>', $lastId];
                    $data = $model->where($where)->limit(0, $perSize)->field('*')->select()->toarray();
                    foreach ($data as $item) {
                        foreach ($item as &$value) {
                            // 加"\t"防止数字导出时变成科学计数
                            $value = iconv('utf-8', 'gbk//IGNORE', $value . "\t");
                        }
                        fputcsv($fp, $item);
                        $lastId = $item['id']; // 用LastId的方式来做分页查询，增加查询效率
                    }
                    unset($data); // 释放变量的内存
                    // 刷新输出缓冲到浏览器，必须同时使用 ob_flush() 和flush() 函数来刷新输出缓冲
                    ob_flush();
                    flush();
                }
                fclose($fp);
            }

            if ($fileNames) {
                // 进行多个文件压缩
                $zip = new \ZipArchive();
                $zipFilePath = config('path.upload_path') . 'zip';
                // 检查文件或目录是否存在
                if (!file_exists($zipFilePath)) {
                    mkdir($zipFilePath, 0777, true);
                }
                $zipFileName = $zipFilePath . '/' . date('YmdHis') . '.zip';
                // 打开压缩包
                $zipRes = $zip->open($zipFileName, \ZipArchive::CREATE); // CREATE 如果指定的zip文件不存在，则新建一个
                if ($zipRes) {
                    foreach ($fileNames as $file) {
                        $zip->addFile($file, basename($file)); // 向压缩包中添加文件
                    }
                }
                $zip->close(); // 关闭压缩包
                foreach ($fileNames as $file) {
                    unlink($file); // 删除csv临时文件
                }

                $newZipFileName = substr($zipFileName, strripos($zipFileName, 'uploads/') + 8);
                $newZipFileName = full_file_url($newZipFileName);
                if ($isDownload) {
                    header('location:' . $newZipFileName);
                    exit;
                } else {
                    return [
                        'status' => 1,
                        'msg' => 'success',
                        'url' => $newZipFileName
                    ];
                }
            }

            $newFileName = substr($fileName, strripos($fileName, 'uploads/') + 8);
            $newFileName = full_file_url($newFileName);
            $fileUrl = $newFileName . $suffix;
            if ($isDownload) {
                header('location:' . $fileUrl);
                exit;
            }

            return [
                'status' => 1,
                'msg' => 'success',
                'url' => $fileUrl
            ];
        } catch (\Exception $e) {
            return [
                'status' => 0,
                'msg' => $e->getMessage()
            ];
        }
    }

    /**
     * 读取Excel
     *
     * @param string $filePath 文件路径
     * @param array $cells
     */
    public static function readExcel($filePath, $cells = [])
    {
        try {
            // 自动类型解析模式比显式模式稍慢
            //$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx'); // 显式模式 -- 设置以Excel5格式(Excel97-2003工作簿)
            //$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
            //$reader->setReadDataOnly(true); // 仅读取数据(指示阅读器忽略样式，数据验证等)
            //$spreadsheet = $reader->load($filePath); // 载入excel文件
            //$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile($filePath);
            $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($filePath); // 自动文件类型解析(当不知道上传的是xls还是xlsx文件)

            /*$sheetCount = $spreadsheet->getSheetCount(); // 工作表个数
            for ($i = 0; $i < $sheetCount; $i ++) {
                //$sheet = $spreadsheet->getSheet($i); // 读取第n个工作表
                //$csv_datas = $sheet->toArray();
                $spreadsheet->setActiveSheetIndex($i);
                $csv_datas = $spreadsheet->getActiveSheet()->toArray();
                dump($csv_datas);die;
            }*/
            $spreadsheet->setActiveSheetIndex(0); // 读取第一个工作表
            if ($cells) {
                // 只读取指定的列
                $csv_datas = [];
                $worksheet = $spreadsheet->getActiveSheet();
                $highestRow = $worksheet->getHighestRow(); // 总行数
                //$highestColumn = $worksheet->getHighestColumn(); // 总列数
                for ($row = 1; $row <= $highestRow; $row++) {
                    $cell_values = [];
                    foreach ($cells as $cell) {
                        // 读取单元格内容
                        $cell_values[] = $worksheet->getCell($cell . $row)->getFormattedValue();
                    }
                    $csv_datas[] = $cell_values;
                }
            } else {
                /*$worksheet = $spreadsheet->getActiveSheet();
                $highestRow = $worksheet->getHighestRow();
                $highestColumn = $worksheet->getHighestColumn();
                $csv_datas = self::yieldData($worksheet, $highestRow, $highestColumn);*/
                $csv_datas = $spreadsheet->getActiveSheet()->toArray();
            }

            return [
                'status' => 1,
                'msg' => 'success',
                'data' => $csv_datas
            ];
        } catch (Exception $e) {
            return [
                'status' => 0,
                'msg' => $e->getMessage()
            ];
        }
    }

    /**
     * 读取Excel【推荐】
     */
    public static function readExcel01($filePath)
    {
        try {
            //$reader = \PhpOffice\PhpSpreadsheet\IOFactory::load($filePath);
            $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile($filePath);
            //$reader->setReadDataOnly(true);
            $spreadsheet = $reader->load($filePath); // 载入excel文件
            //$sheet = $spreadsheet->getSheet(0); // 读取第一个工作表
            $sheet = $spreadsheet->getActiveSheet();

            /*$highestRow = $sheet->getHighestRow(); // 取得总行数
            $highestColumn = $sheet->getHighestColumn(); // 取得总列数
            $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // 取得总列数
            $csv_datas = [];
            for ($row = 1; $row <= $highestRow; $row++) {
                for ($column = 1; $column <= $highestColumnIndex; $column++) {
                    $csv_datas[$row][] = (string)$sheet->getCellByColumnAndRow($column, $row)->getValue();
                }
            }*/

            $csv_datas = [];
            foreach ($sheet->getRowIterator() as $row) {
                $dataRow = [];
                $cellIterator = $row->getCellIterator();
                $cellIterator->setIterateOnlyExistingCells(false);
                foreach ($cellIterator as $cell) {
                    //$cellVal = $cell->getValue(); // 获取cell中数据
                    //$cellVal = $cell->getFormattedValue();
                    $cellVal = $cell->getCalculatedValue();
                    if (!$cellVal) {
                        $cellVal = '';
                    }
                    if (is_object($cellVal)) {
                        $cellVal = $cellVal->__toString(); // 获取到的对象转字符串
                    }
                    // TODO 日期类型会变为数字 $date = date('Y-m-d', ($date - 25569) * 24 * 3600);
                    // 列数多了或者行多了会报错
                    /*$isDate = \PhpOffice\PhpSpreadsheet\Shared\Date::isDateTime($cell);
                    if ($isDate) {
                        $timestamp = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($cellVal);
                        $cellVal = date('Y-m-d H:i:s', $timestamp);
                    }*/

                    //$dataRow[] = trim($cellVal); // getFormattedValue 会有空格出现
                    $dataRow[] = $cellVal; // getCalculatedValue 日期格式会变成数字
                    //$dataRow[] = iconv('gbk', 'utf-8', $cellVal);
                }
                $csv_datas[] = $dataRow;
                //$csv_datas[$row->getRowIndex()] = $dataRow;
            }

            return [
                'status' => 1,
                'msg' => 'success',
                'data' => $csv_datas
            ];
        } catch (\Exception $e) {
            return [
                'status' => 0,
                'msg' => $e->getMessage()
            ];
        }
    }

    /**
     * @param array $options 操作选项
     *        array mergeCells 合并单元格数组
     *        array formula 公式数组
     *        array format 单元格格式数组
     */
    public static function readExcel02($filePath, $cells = [], &$options = [])
    {
        try {
            set_time_limit(300); // 超时
            ini_set('memory_limit', '1024M'); // 内存溢出
            $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
            if (!$reader->canRead($filePath)) {
                $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xls');
                if (!$reader->canRead($filePath)) {
                    //throw new \Exception('只支持导入Excel文件！');
                    return [
                        'status' => 0,
                        'msg' => '只支持导入Excel文件！'
                    ];
                }
            }
            if (empty($options)) {
                // 如果不需要获取特殊操作，则只读内容，可以大幅度提升读取Excel效率
                $reader->setReadDataOnly(true);
            }
            $spreadsheet = $reader->load($filePath); // 载入excel文件
            $sheet = $spreadsheet->getSheet(0); // 读取第一个工作表

            if (isset($options['mergeCells'])) {
                // 读取合并行列
                $options['mergeCells'] = $sheet->getMergeCells();
            }
            $highestColumn = $sheet->getHighestColumn(); // 取得总列数
            $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // 取得总列数
            $highestRow = $sheet->getHighestRow(); // 取得总行数

            $csv_datas = [];
            for ($row = 1; $row <= $highestRow; $row++) {
                $isNull = true;
                for ($column = 1; $column <= $highestColumnIndex; $column++) {
                    $cellName = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($column);
                    $cellId = $cellName . $row;
                    $cell = $sheet->getCell($cellId);

                    if (isset($options['format'])) {
                        // 获取格式
                        $format = $cell->getStyle()->getNumberFormat()->getFormatCode();
                        // 记录格式
                        $options['format'][$row][$cellName] = $format;
                    }
                    if (isset($options['formula'])) {
                        // 获取公式，公式均为=号开头数据
                        $formula = $sheet->getCell($cellId)->getValue();
                        if (0 === strpos($formula, '=')) {
                            $options['formula'][$cellName . $row] = $formula;
                        }
                    }
                    if (isset($format) && 'm/d/yyyy' == $format) {
                        // 日期格式翻转处理
                        $cell->getStyle()->getNumberFormat()->setFormatCode('yyyy/mm/dd');
                    }

                    $csv_datas[$row][$cellName] = trim($sheet->getCell($cellId)->getFormattedValue());
                    if (!empty($csv_datas[$row][$cellName])) {
                        $isNull = false;
                    }
                }
                // 判断是否整行数据为空，是的话删除该行数据
                if ($isNull) {
                    unset($csv_datas[$row]);
                }
            }

            return [
                'status' => 1,
                'msg' => 'success',
                'data' => $csv_datas
            ];
        } catch (\Exception $e) {
            return [
                'status' => 0,
                'msg' => $e->getMessage()
            ];
        }
    }

    /**
     * 数字转字母 （生成Excel列标）
     *
     * @param int $index 索引值
     * @param int $start 字母起始值
     * @return string 返回字母
     */
    public static function intToChr($index, $start = 65)
    {
        if (!is_numeric($index)) {
            return $index;
        }
        $str = '';
        if (floor($index / 26) > 0) {
            $str .= self::intToChr(floor($index / 26) - 1);
        }

        return $str . chr($index % 26 + $start);
    }

    protected static function yieldData($worksheet, $highestRow, $highestColumn)
    {
        for ($row = 1; $row <= $highestRow; $row++) {
            $csv_data = $worksheet->rangeToArray('A' . $row . ':' . $highestColumn . $row); // 读取一行数据

            yield $csv_data[0];
        }
    }
}
